﻿using Dapper;
using Domain.Entity;
using Domain.Entity.In;
using Domain.Entity.ord;
using Domain.Entity.search;
using Domain.Entity.zct;
using Domain.IRepository;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Runtime.Intrinsics.Arm;
using System.Text;
using System.Threading.Tasks;

namespace EFCore.Repository
{
    public class Instock_hRepository : IInstock_hRepository
    {
        //连接字符串
        string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";


        //添加入库单
        public async Task<int> AddInstock_h(Instock_h instock_H)
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"insert into [dbo].[in_instock_h](in_code, in_type, wh_id, wh_name, in_date, in_order, sup_id, sup_name, cust_id, cust_name, linkman, linkphone, in_user, in_categories, in_num, in_total, in_instockOK_num, in_instockNO_num, in_status, create_name, create_time,remark)
                                values('{instock_H.in_code}','{instock_H.in_type}','{instock_H.wh_id}','{instock_H.wh_name}','{instock_H.in_date}','{instock_H.in_order}',{instock_H.sup_id},'{instock_H.sup_name}',{instock_H.cust_id},'{instock_H.cust_name}','{instock_H.linkman}','{instock_H.linkphone}','{instock_H.in_user}',{instock_H.in_categories},{instock_H.in_num},'{instock_H.in_total}',{instock_H.in_instockOK_num},{instock_H.in_instockNO_num},{instock_H.in_status},'{instock_H.create_name}','{instock_H.create_time}','{instock_H.remark}');
                                SELECT CAST(SCOPE_IDENTITY() AS INT);";
                return await sqlConnection.ExecuteScalarAsync<int>(sql);//返回主键id
            }
        }


        //删除入库单
        public async Task<int> DeleteInstock_h(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[in_instock_h] set is_delete=1 where in_id={id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //高级查询
        public async Task<FenYe<Instock_h>> GetAdvancedInstock_h(FenYe<Instock_h> fenYe, string? code, string? starttotal, string? endtotal, string? startdate, string? enddate, string? startnum, string? endnum, int? status, string? type, int? whid, string? createname, string? startcreatetime, string? endcreatetime, string? approvername, string? startapprovertime, string? endapprovertime, string? inuser, string? inorder)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 分页存储过程
                DynamicParameters param = new DynamicParameters();
                param.Add("@pageSize", 10);
                param.Add("@keyName", "create_time DESC");
                param.Add("@tabelName", "vw_instock");
                param.Add("@currentPage", fenYe.CurrentPage);
                param.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                //判断条件
                string sqlparam = $" is_delete=0 ";
                if (!string.IsNullOrEmpty(code))
                    sqlparam += $" and in_code like '%{code}%'";
                if (!string.IsNullOrEmpty(starttotal) || !string.IsNullOrEmpty(endtotal))
                    sqlparam += $" and  in_total between '{starttotal}' and '{endtotal}'";
                if (!string.IsNullOrEmpty(startdate) || !string.IsNullOrEmpty(enddate))
                    sqlparam += $" and  in_date between '{startdate}' and '{enddate}'";
                if (!string.IsNullOrEmpty(startnum) || !string.IsNullOrEmpty(endnum))
                    sqlparam += $" and  in_num between '{startnum}' and '{endnum}'";
                if (status != null && status != 0)
                    sqlparam += $" and  in_status={status}";
                if (!string.IsNullOrEmpty(type))
                    sqlparam += $" and in_type like '%{type}%'";
                if (whid != null && whid != 0)
                    sqlparam += $" and  wh_id={whid}";
                if (!string.IsNullOrEmpty(createname))
                    sqlparam += $" and create_name like '%{createname}%'";
                if (!string.IsNullOrEmpty(startcreatetime) || !string.IsNullOrEmpty(endcreatetime))
                    sqlparam += $" and  create_time between '{startcreatetime}' and '{endcreatetime}'";
                if (!string.IsNullOrEmpty(approvername))
                    sqlparam += $" and approver_name like '%{approvername}%'";
                if (!string.IsNullOrEmpty(startapprovertime) || !string.IsNullOrEmpty(endapprovertime))
                    sqlparam += $" and  approver_time between '{startapprovertime}' and '{endapprovertime}'";
                if (!string.IsNullOrEmpty(inuser))
                    sqlparam += $" and in_user like '%{inuser}%'";
                if (!string.IsNullOrEmpty(inorder))
                    sqlparam += $" and in_order like '%{inorder}%'";

                param.Add("@where", sqlparam);

                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                List<Instock_h> list = sqlConnection.Query<Instock_h>(sql, param).ToList();
                fenYe.List = list;
                fenYe.Rows = param.Get<int>("@rows");
                return fenYe;
            }
        }


        //根据货品id查询入库记录
        public async Task<List<Instock_h>> GetAllInstock_h(int procid)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select h.in_id,in_code,in_type,in_date,wh_name,in_num,in_total,in_user,create_name,create_time from [dbo].[in_instock_d] d
                                inner join [dbo].[in_instock_h] h on h.in_id=d.in_id
                                where proc_id={procid}";
                return sqlConnection.Query<Instock_h>(sql).ToList();
            }
        }


        //根据关联单号查询入库记录
        public async Task<List<Instock_h>> GetAllInstock_hByCode(string code)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select * from vw_instock where in_order='{code}'";
                return sqlConnection.Query<Instock_h>(sql).ToList();
            }
        }


        //分页查询
        public async Task<FenYe<Instock_h>> GetInstock_h(FenYe<Instock_h> fenYe, string? code, string? type, int? status, int? whid)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 分页存储过程
                DynamicParameters param = new DynamicParameters();
                param.Add("@pageSize", 10);
                param.Add("@keyName", "create_time DESC");
                param.Add("@tabelName", "vw_instock");
                param.Add("@currentPage", fenYe.CurrentPage);
                param.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                //判断条件
                string sqlparam = $" is_delete=0 ";
                if (!string.IsNullOrEmpty(code))
                    sqlparam += $" and in_code like '%{code}%'";
                if (!string.IsNullOrEmpty(type))
                    sqlparam += $" and in_type like '%{type}%'";
                if (status != null && status != 0)
                    sqlparam += $" and  in_status={status}";
                if (whid != null && whid != 0)
                    sqlparam += $" and  wh_id={whid}";

                param.Add("@where", sqlparam);

                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                List<Instock_h> list = sqlConnection.Query<Instock_h>(sql, param).ToList();
                fenYe.List = list;
                fenYe.Rows = param.Get<int>("@rows");
                return fenYe;
            }
        }

        //根据id查询入库单
        public async Task<Instock_h> GetInstock_hById(int instockid)
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"select * from [dbo].[in_instock_h] where in_id={instockid}";
                return await sqlConnection.QueryFirstAsync<Instock_h>(sql);
            }
        }

        public async Task<FenYe<Instock_h>> GetInstock_hCust(FenYe<Instock_h> fenYe, int cust_id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 分页存储过程
                DynamicParameters param = new DynamicParameters();
                param.Add("@pageSize", 10);
                param.Add("@keyName", "in_id");
                param.Add("@tabelName", "vw_instock");
                param.Add("@currentPage", fenYe.CurrentPage);
                param.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                //判断条件
                string sqlparam = $" is_delete=0 and cust_id={cust_id}";

                param.Add("@where", sqlparam);

                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                List<Instock_h> list = sqlConnection.Query<Instock_h>(sql, param).ToList();
                fenYe.List = list;
                fenYe.Rows = param.Get<int>("@rows");
                return fenYe;
            }
        }

        public async Task<FenYe<Instock_h>> GetInstock_hSup(FenYe<Instock_h> fenYe, int sup_id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 分页存储过程
                DynamicParameters param = new DynamicParameters();
                param.Add("@pageSize", 10);
                param.Add("@keyName", "in_id");
                param.Add("@tabelName", "vw_instock");
                param.Add("@currentPage", fenYe.CurrentPage);
                param.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                //判断条件
                string sqlparam = $" is_delete=0 and sup_id={sup_id}";

                param.Add("@where", sqlparam);

                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                List<Instock_h> list = sqlConnection.Query<Instock_h>(sql, param).ToList();
                fenYe.List = list;
                fenYe.Rows = param.Get<int>("@rows");
                return fenYe;
            }
        }

        //入库统计
        public async Task<Instock_hTJResult> Instock_hTJ(string? timeType, string? code, int? whid,
            string? instarttime, string? inendtime, string? starttime, string? endtime,
            string? inType, int? page, int? pagesize)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                var where = " h.is_delete = 0 ";
                var parameters = new DynamicParameters();

                // 入库时间条件
                if (!string.IsNullOrEmpty(instarttime) && !string.IsNullOrEmpty(inendtime))
                {
                    where += " AND h.in_date BETWEEN @instarttime AND @inendtime";
                    parameters.Add("@instarttime", instarttime);
                    parameters.Add("@inendtime", inendtime);
                }

                // 入库单号条件
                if (!string.IsNullOrEmpty(code))
                {
                    where += " AND h.in_code LIKE @code";
                    parameters.Add("@code", $"%{code}%");
                }

                // 仓库条件
                if (whid != null && whid != 0)
                {
                    where += " AND h.wh_id = @whid";
                    parameters.Add("@whid", whid);
                }

                // 入库类型条件
                if (!string.IsNullOrEmpty(inType))
                {
                    where += " AND h.in_type = @inType";
                    parameters.Add("@inType", inType);
                }

                if (!string.IsNullOrEmpty(timeType))
                {
                    switch (timeType)
                    {
                        case "today":
                            where += " AND CONVERT(date, h.in_date) = CONVERT(date, GETDATE())";
                            break;
                        case "yesterday":
                            where += " AND CONVERT(date, h.in_date) = CONVERT(date, DATEADD(day, -1, GETDATE()))";
                            break;
                        case "week":
                            where += " AND DATEPART(week, h.in_date) = DATEPART(week, GETDATE()) AND YEAR(h.in_date) = YEAR(GETDATE())";
                            break;
                        case "lastWeek":
                            where += " AND DATEPART(week, h.in_date) = DATEPART(week, DATEADD(week, -1, GETDATE()))"
                                  + " AND YEAR(h.in_date) = YEAR(DATEADD(week, -1, GETDATE()))";
                            break;
                        case "month":
                            where += " AND MONTH(h.in_date) = MONTH(GETDATE()) AND YEAR(h.in_date) = YEAR(GETDATE())";
                            break;
                        case "lastMonth":
                            where += " AND MONTH(h.in_date) = MONTH(DATEADD(month, -1, GETDATE()))"
                                  + " AND YEAR(h.in_date) = YEAR(DATEADD(month, -1, GETDATE()))";
                            break;
                    }
                }

                int skip = ((page ?? 1) - 1) * (pagesize ?? 10);

                // 1. 查询总数（只查主单表）
                string countSql = $@"
            SELECT COUNT(1)
            FROM [dbo].[in_instock_h] h
            WHERE {where}
        ";
                int total = await sqlConnection.ExecuteScalarAsync<int>(countSql, parameters);

                // 2. 查询统计（全量，不分页）
                string statSql = $@"
            SELECT 
                ISNULL(SUM(h.in_total), 0) AS totalAmount,
                ISNULL(SUM(h.in_num), 0) AS totalNum,
                COUNT(DISTINCT h.in_user) AS totalUser,
                COUNT(DISTINCT h.in_code) AS totalOrder
            FROM [dbo].[in_instock_h] h
            WHERE {where}
        ";
                var stat = await sqlConnection.QueryFirstOrDefaultAsync<dynamic>(statSql, parameters);

                // 3. 查询当前页数据
                string sql = $@"
            SELECT 
                h.in_code, 
                h.in_date, 
                h.in_type, 
                h.wh_id, 
                h.in_num, 
                h.in_total, 
                h.in_user,
                h.create_name,
                h.create_time
            FROM [dbo].[in_instock_h] h
            WHERE {where}
            ORDER BY h.in_date DESC, h.in_code
            OFFSET @skip ROWS FETCH NEXT @pageSize ROWS ONLY";

                parameters.Add("@skip", skip);
                parameters.Add("@pagesize", pagesize ?? 10);

                var list = (await sqlConnection.QueryAsync<Instock_hDto>(sql, parameters)).ToList();

                return new Instock_hTJResult
                {
                    list = list,
                    total = total,
                    in_total = stat?.in_total ?? 0,//入库金额
                    in_num = stat?.in_num ?? 0,//入库数量
                };
            }
        }


        //修改入库单
        public async Task<int> UpdateInstock_h(Instock_h instock_H)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[in_instock_h] set wh_id={instock_H.wh_id},wh_name='{instock_H.wh_name}',in_date='{instock_H.in_date}',in_order='{instock_H.in_order}',
                                sup_id={instock_H.sup_id},sup_name='{instock_H.sup_name}',cust_id={instock_H.cust_id},cust_name='{instock_H.cust_name}',linkman='{instock_H.linkman}',linkphone='{instock_H.linkphone}',
                                in_user='{instock_H.in_user}',in_categories={instock_H.in_categories},in_num={instock_H.in_num},in_total='{instock_H.in_total}',in_instockOK_num={instock_H.in_instockOK_num},in_instockNO_num={instock_H.in_instockNO_num},
                                in_status={instock_H.in_status},update_name='{instock_H.update_name}',update_time='{instock_H.update_time}',remark='{instock_H.remark}' where in_id={instock_H.in_id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //修改单据状态
        public async Task<int> UpdateStatus(int instockid, int status, string approvername, string approvertime)
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[in_instock_h] set in_status={status} ,approver_name='{approvername}',approver_time='{approvertime}' where in_id={instockid}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //修改状态
        public async Task<int> UpdateStatus(int instockid, int status)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[in_instock_h] set in_status={status} where in_id={instockid}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }
    }
}
